﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Collections;

/*********************************************************
 * 
 * programmer:		patrick.woolhoo@gmail.com
 * svn:				http://woolhoo-net.googlecode.com/svn/trunk/wnet/
 * class:			Db
 * desc:			操作数据库的基础类，一般不需要直接调用，主要供Model调用，
 *					分页功能需要建立下面的两个存储过程
 * 
 * *******************************************************/

namespace Patrick.Custom {
	/// <summary>
	/// Db 的摘要说明
	/// </summary>
	public class Db {
		public Db() {
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}




		#region GetConnection()

		/// <summary>
		/// GetConnection() 返回新建的连接对象
		/// </summary>
		/// <returns></returns>
		public static SqlConnection GetConnection(string Connstr) {
			return new SqlConnection(Connstr);
		}

		#endregion



		#region TableExist()

		/// <summary>
		///  测试数据库中是否包含某个表
		/// </summary>
		/// <param name="Table"></param>
		/// <returns></returns>
		public static bool TableExist(string Table) {
			string ConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
			return TableExist(Table, ConnStr);
		}


		public static bool TableExist(string Table, string ConnStr) {
			using (SqlConnection Conn = new SqlConnection(ConnStr)) {
				return TableExist(Table, Conn);
			}
		}


		public static bool TableExist(string Table, SqlConnection Conn) {
			bool RetValue = false;
			bool OpenedHere = false;

			string sql = "SELECT COUNT(*) FROM [Information_Schema].[Tables] WHERE [TABLE_NAME] = @table_name";
			using (SqlCommand Cmd = new SqlCommand(sql, Conn)) {
				Cmd.CommandType = CommandType.Text;
				Cmd.Parameters.Add("table_name", SqlDbType.NVarChar).Value = Table;
				try {
					if (Conn.State != ConnectionState.Open) {
						Conn.Open();
						OpenedHere = true;
					}
					RetValue = Convert.ToInt32(Cmd.ExecuteScalar()) > 0;
				} catch (Exception e) {
					return false;
				} finally {
					if (OpenedHere) if (Conn.State == ConnectionState.Open) Conn.Close();
				}
			}
			return RetValue;
		}


		public static bool TableExist(string Table, SqlTransaction Trans) {
			bool RetValue = false;

			string sql = "SELECT COUNT(*) FROM [Information_Schema].[Tables] WHERE [TABLE_NAME] = @table_name";
			using (SqlCommand Cmd = new SqlCommand(sql, Trans.Connection, Trans)) {
				Cmd.CommandType = CommandType.Text;
				Cmd.Parameters.Add("table_name", SqlDbType.NVarChar).Value = Table;
				try {
					RetValue = Convert.ToInt32(Cmd.ExecuteScalar()) > 0;
				} catch {
					return false;
				}
			}
			return RetValue;
		}

		#endregion



		#region TableSchema()

		/// <summary>
		/// TableSchema() 取得某个表的结构
		/// </summary>
		/// <param name="Table"></param>
		/// <returns></returns>
		public static DataTable TableSchema(string Table) {
			string ConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
			return TableSchema(Table, ConnStr);
		}


		public static DataTable TableSchema(string Table, string ConnStr) {
			using (SqlConnection Conn = new SqlConnection(ConnStr)) {
				return TableSchema(Table, Conn);
			}
		}


		public static DataTable TableSchema(string Table, SqlConnection Conn) {
			DataSet ds = new DataSet();
			bool OpenedHere = false;

			using (SqlCommand Cmd = new SqlCommand("sp_columns", Conn)) {
				Cmd.CommandType = CommandType.StoredProcedure;
				Cmd.Parameters.Add("table_name", SqlDbType.NVarChar).Value = Table;
				SqlDataAdapter ad = new SqlDataAdapter(Cmd);
				try {
					if (Conn.State != ConnectionState.Open) {
						Conn.Open();
						OpenedHere = true;
					}
					ad.Fill(ds);
				} catch {
					return null;
				} finally {
					if (OpenedHere) if (Conn.State == ConnectionState.Open) Conn.Close();
				}
			}
			return (DataTable)ds.Tables[0];
		}


		public static DataTable TableSchema(string Table, SqlTransaction Trans) {
			DataSet ds = new DataSet();

			using (SqlCommand Cmd = new SqlCommand("sp_columns", Trans.Connection, Trans)) {
				Cmd.CommandType = CommandType.StoredProcedure;
				Cmd.Parameters.Add("table_name", SqlDbType.NVarChar).Value = Table;
				SqlDataAdapter ad = new SqlDataAdapter(Cmd);
				try {
					ad.Fill(ds);
				} catch {
					return null;
				}
			}
			return (DataTable)ds.Tables[0];
		}

		#endregion



		#region QueryTable()

		/// <summary>
		/// QueryTable() 执行自定义的 sql(SELECT) 语句并返回 DataTable 类型的结果
		/// </summary>
		/// <param name="Sql"></param>
		/// <returns></returns>
		public static DataTable QueryTable(string Sql) {
			string ConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
			return QueryTable(Sql, ConnStr);
		}


		public static DataTable QueryTable(string Sql, string ConnStr) {
			SqlConnection Conn = new SqlConnection(ConnStr);
			return QueryTable(Sql, Conn);
		}


		public static DataTable QueryTable(string Sql, SqlConnection Conn) {
			DataSet ds = new DataSet();
			bool OpenedHere = false;

			using (SqlCommand Cmd = new SqlCommand(Sql, Conn)) {
				Cmd.CommandType = CommandType.Text;
				SqlDataAdapter ad = new SqlDataAdapter(Cmd);
				try {
					if (Conn.State != ConnectionState.Open) {
						Conn.Open();
						OpenedHere = true;
					}
					ad.Fill(ds);
				} catch {
					return null;
				} finally {
					if (OpenedHere) if (Conn.State == ConnectionState.Open) Conn.Close();
				}
			}
			return ds.Tables[0];
		}


		public static DataTable QueryTable(string Sql, SqlTransaction Trans) {
			DataSet ds = new DataSet();

			using (SqlCommand Cmd = new SqlCommand(Sql, Trans.Connection, Trans)) {
				Cmd.CommandType = CommandType.Text;
				SqlDataAdapter ad = new SqlDataAdapter(Cmd);
				try {
					ad.Fill(ds);
				} catch {
					return null;
				}
			}
			return (DataTable)ds.Tables[0];
		}

		#endregion



		#region QueryScalar()

		/// <summary>
		/// QueryScalar() 执行自定义的 sql 语句并返回结果集首字段
		/// </summary>
		/// <param name="Sql"></param>
		/// <returns></returns>
		public static object QueryScalar(string Sql) {
			string ConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
			return QueryScalar(Sql, ConnStr);
		}


		public static object QueryScalar(string Sql, string ConnStr) {
			SqlConnection Conn = new SqlConnection(ConnStr);
			return QueryScalar(Sql, Conn);
		}


		public static object QueryScalar(string Sql, SqlConnection Conn) {
			object RetValue = "";
			bool OpenedHere = false;

			using (SqlCommand Cmd = new SqlCommand(Sql, Conn)) {
				Cmd.CommandType = CommandType.Text;
				try {
					if (Conn.State != ConnectionState.Open) {
						Conn.Open();
						OpenedHere = true;
					}
					RetValue = Cmd.ExecuteScalar().ToString();
				} catch {
					return null;
				} finally {
					if (OpenedHere) if (Conn.State == ConnectionState.Open) Conn.Close();
				}
			}
			return RetValue;
		}


		public static object QueryScalar(string Sql, SqlTransaction Trans) {
			object RetValue = "";

			using (SqlCommand Cmd = new SqlCommand(Sql, Trans.Connection, Trans)) {
				Cmd.CommandType = CommandType.Text;
				try {
					RetValue = Cmd.ExecuteScalar();
				} catch {
					return "";
				}
			}
			return RetValue;
		}

		#endregion



		#region Query()

		/// <summary>
		/// Query() 执行自定义的 sql(INSERT, DELETE, UPDATE) 语句并返回 受影响的行数
		/// </summary>
		/// <param name="Sql"></param>
		/// <returns></returns>
		public static int Query(string Sql) {
			string ConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
			return Query(Sql, ConnStr);
		}


		public static int Query(string Sql, string ConnStr) {
			SqlConnection Conn = new SqlConnection(ConnStr);
			return Query(Sql, Conn);
		}


		public static int Query(string Sql, SqlConnection Conn) {
			int RetValue = 0;
			bool OpenedHere = false;

			using (SqlCommand Cmd = new SqlCommand(Sql, Conn)) {
				Cmd.CommandType = CommandType.Text;
				try {
					if (Conn.State != ConnectionState.Open) {
						Conn.Open();
						OpenedHere = true;
					}
					RetValue = Cmd.ExecuteNonQuery();
				} catch {
					return 0;
				} finally {
					if (OpenedHere) if (Conn.State == ConnectionState.Open) Conn.Close();
				}
			}
			return RetValue;
		}


		public static int Query(string Sql, SqlTransaction Trans) {
			int RetValue = 0;
			bool OpenedHere = false;

			using (SqlCommand Cmd = new SqlCommand(Sql, Trans.Connection, Trans)) {
				Cmd.CommandType = CommandType.Text;
				try {
					if (Trans.Connection.State != ConnectionState.Open) {
						Trans.Connection.Open();
						OpenedHere = true;
					}
					RetValue = Cmd.ExecuteNonQuery();
				} catch {
					return 0;
				} finally {
					if (OpenedHere) if (Trans.Connection.State == ConnectionState.Open) Trans.Connection.Close();
				}
			}
			return RetValue;
		}

		#endregion



		#region QueryPagedTable()

		/// <summary>
		/// QueryPagedTable() 返回分页的查询结果
		/// </summary>
		/// <param name="PageSize"></param>
		/// <param name="PageNo"></param>
		/// <param name="Table"></param>
		/// <param name="Filter"></param>
		/// <param name="Order"></param>
		/// <returns>返回DataTable</returns>

		/*
		CREATE PROC QueryPagedTable 
			@PageSize	int,
			@PageNo		int,
			@Table		sysname,
			@Filter		nvarchar(1000),
			@Order		nvarchar(500)
		AS

		DECLARE @Total int
		SET @Total = @PageSize * @PageNo


		-- 计算列表的起止位置
		DECLARE @Start int
		SET @Start = (@PageNo - 1) * @PageSize

		DECLARE @w NVARCHAR(1000)
		IF (LEN(@Filter) > 0)
			SET @w = ' WHERE ' + @Filter

		DECLARE @o NVARCHAR(500)
		IF (LEN(@Order) > 0)
			SET @o = ' ORDER BY ' + @Order


		-- 建立临时表
		SET ROWCOUNT @Total
		EXEC ('SELECT * INTO ##TEMP123 FROM [' + @Table + '] ' + @w + @o)
		SET ROWCOUNT 0

		-- 将临时表中不在范围内的数据排除
		SET ROWCOUNT @Start
		IF @Start > 0
			DELETE FROM ##TEMP123
		SET ROWCOUNT 0

		SELECT * FROM ##TEMP123
		DROP TABLE ##TEMP123
		GO

		*/

		public static DataTable QueryPagedTable(int PageSize, int PageNo, string Table, string Filter, string Order) {
			string ConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
			return QueryPagedTable(PageSize, PageNo, Table, ConnStr, Filter, Order);
		}


		public static DataTable QueryPagedTable(int PageSize, int PageNo, string Table, string ConnStr, string Filter, string Order) {
			using (SqlConnection Conn = new SqlConnection(ConnStr)) {
				return QueryPagedTable(PageSize, PageNo, Table, Conn, Filter, Order);
			}
		}


		public static DataTable QueryPagedTable(int PageSize, int PageNo, string Table, SqlConnection Conn, string Filter, string Order) {
			DataSet ds = new DataSet();
			bool OpenedHere = false;

			using (SqlCommand Cmd = new SqlCommand("QueryPagedTable", Conn)) {
				Cmd.CommandType = CommandType.StoredProcedure;
				Cmd.Parameters.Add("PageSize", SqlDbType.Int).Value = PageSize;
				Cmd.Parameters.Add("PageNo", SqlDbType.Int).Value = PageNo;
				Cmd.Parameters.Add("Table", SqlDbType.NVarChar).Value = Table;
				Cmd.Parameters.Add("Filter", SqlDbType.NVarChar).Value = Filter;
				Cmd.Parameters.Add("Order", SqlDbType.NVarChar).Value = Order == null ? "" : Order;
				SqlDataAdapter ad = new SqlDataAdapter(Cmd);

				try {
					if (Conn.State != ConnectionState.Open) {
						Conn.Open();
						OpenedHere = true;
					}
					ad.Fill(ds);
				} catch {
					return null;
				} finally {
					if (OpenedHere) if (Conn.State == ConnectionState.Open) Conn.Close();
				}
			}
			return ds.Tables[0];
		}



		public static DataTable QueryPagedTable(int PageSize, int PageNo, string Table, SqlTransaction Trans, string Filter, string Order) {
			DataSet ds = new DataSet();

			using (SqlCommand Cmd = new SqlCommand("QueryPagedTable", Trans.Connection, Trans)) {
				Cmd.CommandType = CommandType.StoredProcedure;
				Cmd.Parameters.Add("PageSize", SqlDbType.Int).Value = PageSize;
				Cmd.Parameters.Add("PageNo", SqlDbType.Int).Value = PageNo;
				Cmd.Parameters.Add("Table", SqlDbType.NVarChar).Value = Table;
				Cmd.Parameters.Add("Filter", SqlDbType.NVarChar).Value = Filter;
				Cmd.Parameters.Add("Order", SqlDbType.NVarChar).Value = Order;
				SqlDataAdapter ad = new SqlDataAdapter(Cmd);

				try {
					ad.Fill(ds);
				} catch {
					return null;
				}
			}
			return ds.Tables[0];
		}




		/// <summary>
		/// 返回分页的查询结果
		/// </summary>
		/// <param name="PageSize"></param>
		/// <param name="PageNo"></param>
		/// <param name="SubQuery"></param>
		/// <returns>返回DataTable</returns>

		/*
		CREATE PROC QueryCustomPagedTable 
			@PageSize	int,
			@PageNo		int,
			@SubQuery	nvarchar(1000),
			@Order		nvarchar(500)

		AS 
		-- 建立临时表
			CREATE TABLE #TEMP (
				[idx]		int		primary key identity(1, 1),
				[id]		int
			)

		-- 插入临时表
			DECLARE @Insert AS nvarchar(4000)
			SET @Insert = 'INSERT INTO #TEMP ([id]) '
			SET @Insert = @Insert + 'SELECT TOP ' + CAST((@PageSize * @PageNo) AS nvarchar(15)) + ' [id] FROM (' + @SubQuery + ') AS [T]'

			EXEC sp_executesql @Insert

		-- 计算列表的起止位置
			DECLARE @Start AS int
			SET @Start = (@PageNo - 1) * @PageSize

		-- 返回结果
			DECLARE @Select AS nvarchar(4000)
			SET @Select = 'SELECT * FROM (' + @SubQuery + ') AS [T] WHERE [T].[id] IN (SELECT [id] FROM #TEMP WHERE [idx] > ' + CAST(@Start AS nvarchar(15)) + ')'
			IF (LEN(@Order) > 0)
				SET @Select = @Select + ' ORDER BY ' + @Order

			EXEC sp_executesql @Select

			DROP TABLE #TEMP
		*/

		public static DataTable QueryPagedTable(int PageSize, int PageNo, string SubQuery, string Order) {
			DataSet ds = new DataSet();
			using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString)) {
				using (SqlCommand Cmd = new SqlCommand("QueryCustomPagedTable", Conn)) {
					Cmd.CommandType = CommandType.StoredProcedure;
					Cmd.Parameters.Add("PageSize", SqlDbType.Int).Value = PageSize;
					Cmd.Parameters.Add("PageNo", SqlDbType.Int).Value = PageNo;
					Cmd.Parameters.Add("SubQuery", SqlDbType.NVarChar).Value = SubQuery;
					Cmd.Parameters.Add("Order", SqlDbType.NVarChar).Value = Order;
					Conn.Open();
					SqlDataAdapter ad = new SqlDataAdapter(Cmd);
					ad.Fill(ds);
				}
				Conn.Close();
			}
			return ds.Tables[0];
		}



		#endregion

	}
}